﻿using PayexpDAL;
using SpreadsheetLight;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Utilities;

namespace ExcelHandler
{
    public class BankStatementExportToExcel : ExportToExcelBase
    {
        private BankAccount _bankAccount; 

        public BankStatementExportToExcel(string destinationFileFullPath, bool batchMode, BankAccount bankAccount)
            : base(destinationFileFullPath, batchMode)
        {
            if (bankAccount == null)
            {
                throw new Exception("Please provide the Bank Account");
            }

            this._bankAccount = bankAccount;

            CreateSummary(); // I cannot put this in the base class :)
        }

        protected override void CreateSummary()
        {
            base.CreateSummary();

            this._currentDataRowIndex++; // VERY IMPORTANT! :)
            string rowIndexString = this._currentDataRowIndex.ToString();
            this._slDocument.SetCellValue("A" + rowIndexString, "Report Type");
            this._slDocument.SetCellStyle("A" + rowIndexString, "B" + rowIndexString, this._summaryKeyStyle);
            this._slDocument.MergeWorksheetCells("A" + rowIndexString, "B" + rowIndexString);
            this._slDocument.MergeWorksheetCells("C" + rowIndexString, "E" + rowIndexString);
            this._slDocument.SetCellValue("C" + rowIndexString, "Bank Statement");
            this._slDocument.SetCellStyle("C" + rowIndexString, "E" + rowIndexString, this._summaryValueStyle);
            //this._slDocument.AutoFitRow(this._currentDataRowIndex);

            this._currentDataRowIndex++; // VERY IMPORTANT! :)
            rowIndexString = this._currentDataRowIndex.ToString();
            this._slDocument.SetCellValue("A" + rowIndexString, "Bank Name");
            this._slDocument.SetCellStyle("A" + rowIndexString, "B" + rowIndexString, this._summaryKeyStyle);
            this._slDocument.MergeWorksheetCells("A" + rowIndexString, "B" + rowIndexString);
            this._slDocument.MergeWorksheetCells("C" + rowIndexString, "E" + rowIndexString);
            this._slDocument.SetCellValue("C" + rowIndexString, this._bankAccount.BankDisplayName);
            this._slDocument.SetCellStyle("C" + rowIndexString, "E" + rowIndexString, this._summaryValueStyle);
            //this._slDocument.AutoFitRow(this._currentDataRowIndex);

            this._currentDataRowIndex++; // VERY IMPORTANT! :)
            rowIndexString = this._currentDataRowIndex.ToString();
            this._slDocument.SetCellValue("A" + rowIndexString, "Bank Details");
            this._slDocument.SetCellStyle("A" + rowIndexString, "B" + rowIndexString, this._summaryKeyStyle);
            this._slDocument.MergeWorksheetCells("A" + rowIndexString, "B" + rowIndexString);
            this._slDocument.MergeWorksheetCells("C" + rowIndexString, "E" + rowIndexString);
            string bankDetails = string.Format("A/c No: {0}{1}IFSC: {2}{3}MICR Code: {4}",
                                                   this._bankAccount.AccountNumber,
                                                   Environment.NewLine,
                                                   this._bankAccount.IFSCCode,
                                                   Environment.NewLine,
                                                   this._bankAccount.MICRCode);
            this._slDocument.SetCellValue("C" + rowIndexString, bankDetails);
            this._slDocument.SetCellStyle("C" + rowIndexString, "E" + rowIndexString, this._summaryValueStyle);
            //this._slDocument.AutoFitRow(this._currentDataRowIndex);

            // this is just a hack becuase merged cells and wrap do not work in excel...known issue :(
            this._slDocument.SetCellValue("CK" + rowIndexString, bankDetails);
            this._slDocument.SetCellStyle("CK" + rowIndexString, this._summaryValueStyle);
            this._slDocument.SetColumnWidth("CK", 50);


            this._currentDataRowIndex++; // VERY IMPORTANT! :)
            rowIndexString = this._currentDataRowIndex.ToString();
            this._slDocument.SetCellValue("A" + rowIndexString, "Statement Period");
            this._slDocument.SetCellStyle("A" + rowIndexString, "B" + rowIndexString, this._summaryKeyStyle);
            this._slDocument.MergeWorksheetCells("A" + rowIndexString, "B" + rowIndexString);
            this._slDocument.MergeWorksheetCells("C" + rowIndexString, "E" + rowIndexString);
            string cellValue = Utils.April.Get_DD_MONTH_YY_DateString() + " to " + DateTime.Today.Get_DD_MONTH_YY_DateString();
            this._slDocument.SetCellValue("C" + rowIndexString, cellValue);
            this._slDocument.SetCellStyle("C" + rowIndexString, "E" + rowIndexString, this._summaryValueStyle);
            //this._slDocument.AutoFitRow(this._currentDataRowIndex);

            this._currentDataRowIndex++; // VERY IMPORTANT! :)
            rowIndexString = this._currentDataRowIndex.ToString();
            this._slDocument.SetCellValue("A" + rowIndexString, "Opening Balance");
            this._slDocument.SetCellStyle("A" + rowIndexString, "B" + rowIndexString, this._summaryKeyStyle);
            this._slDocument.MergeWorksheetCells("A" + rowIndexString, "B" + rowIndexString);
            this._slDocument.MergeWorksheetCells("C" + rowIndexString, "E" + rowIndexString);
            SetDecimalFormatCode(this._summaryValueStyle);
            this._slDocument.SetCellValue("C" + rowIndexString, this._bankAccount.FinancialYearOpeningBalance);
            this._slDocument.SetCellStyle("C" + rowIndexString, "E" + rowIndexString, this._summaryValueStyle);
            ResetFormatCode(this._summaryValueStyle);
            //this._slDocument.AutoFitRow(this._currentDataRowIndex);

            this._currentDataRowIndex++; // VERY IMPORTANT! :)
            rowIndexString = this._currentDataRowIndex.ToString();
            this._slDocument.SetCellValue("A" + rowIndexString, "Running Balance");
            this._slDocument.SetCellStyle("A" + rowIndexString, "B" + rowIndexString, this._summaryKeyStyle);
            this._slDocument.MergeWorksheetCells("A" + rowIndexString, "B" + rowIndexString);
            this._slDocument.MergeWorksheetCells("C" + rowIndexString, "E" + rowIndexString);
            SetDecimalFormatCode(this._summaryValueStyle);
            this._slDocument.SetCellValue("C" + rowIndexString, this._bankAccount.ReonciledBalance);
            this._slDocument.SetCellStyle("C" + rowIndexString, "E" + rowIndexString, this._summaryValueStyle);
            ResetFormatCode(this._summaryValueStyle);
            //this._slDocument.AutoFitRow(this._currentDataRowIndex);

            this._currentDataRowIndex++; // VERY IMPORTANT! :)
            rowIndexString = this._currentDataRowIndex.ToString();
            this._slDocument.SetCellValue("A" + rowIndexString, "Available Balance");
            this._slDocument.SetCellStyle("A" + rowIndexString, "B" + rowIndexString, this._summaryKeyStyle);
            this._slDocument.MergeWorksheetCells("A" + rowIndexString, "B" + rowIndexString);
            this._slDocument.MergeWorksheetCells("C" + rowIndexString, "E" + rowIndexString);
            SetDecimalFormatCode(this._summaryValueStyle);
            this._slDocument.SetCellValue("C" + rowIndexString, this._bankAccount.AvailableBalance);
            this._slDocument.SetCellStyle("C" + rowIndexString, "E" + rowIndexString, this._summaryValueStyle);
            ResetFormatCode(this._summaryValueStyle);
            //this._slDocument.AutoFitRow(this._currentDataRowIndex);

            this._currentDataRowIndex++; // VERY IMPORTANT! :)
            rowIndexString = this._currentDataRowIndex.ToString();
            this._slDocument.SetCellValue("A" + rowIndexString, "No Of Unreconciled Expense Txns");
            this._slDocument.SetCellStyle("A" + rowIndexString, "B" + rowIndexString, this._summaryKeyStyle);
            this._slDocument.MergeWorksheetCells("A" + rowIndexString, "B" + rowIndexString);
            this._slDocument.MergeWorksheetCells("C" + rowIndexString, "E" + rowIndexString);
            if (this._bankAccount.NoOfUnreconciledExpenseTransactions.HasValue)
            {
                this._slDocument.SetCellValue("C" + rowIndexString, this._bankAccount.NoOfUnreconciledExpenseTransactions.Value);
            }
            else
            {
                this._slDocument.SetCellValue("C" + rowIndexString, "Data Not Available");
            }
            this._slDocument.SetCellStyle("C" + rowIndexString, "E" + rowIndexString, this._summaryValueStyle);
            //this._slDocument.AutoFitRow(this._currentDataRowIndex);
            
            this._currentDataRowIndex++; // VERY IMPORTANT! :)
            rowIndexString = this._currentDataRowIndex.ToString();
            this._slDocument.SetCellValue("A" + rowIndexString, "No Of Unreconciled Income Txns");
            this._slDocument.SetCellStyle("A" + rowIndexString, "B" + rowIndexString, this._summaryKeyStyle);
            this._slDocument.MergeWorksheetCells("A" + rowIndexString, "B" + rowIndexString);
            this._slDocument.MergeWorksheetCells("C" + rowIndexString, "E" + rowIndexString);
            if (this._bankAccount.NoOfUnreconciledIncomeTransactions.HasValue)
            {
                this._slDocument.SetCellValue("C" + rowIndexString, this._bankAccount.NoOfUnreconciledIncomeTransactions.Value);
            }
            else
            {
                this._slDocument.SetCellValue("C" + rowIndexString, "Data Not Available");
            }
            this._slDocument.SetCellStyle("C" + rowIndexString, "E" + rowIndexString, this._summaryValueStyle);
            //this._slDocument.AutoFitRow(this._currentDataRowIndex);

            this._currentDataRowIndex++; // VERY IMPORTANT! :)
            rowIndexString = this._currentDataRowIndex.ToString();
            this._slDocument.SetCellValue("A" + rowIndexString, "Last Reconciled On");
            this._slDocument.SetCellStyle("A" + rowIndexString, "B" + rowIndexString, this._summaryKeyStyle);
            this._slDocument.MergeWorksheetCells("A" + rowIndexString, "B" + rowIndexString);
            this._slDocument.MergeWorksheetCells("C" + rowIndexString, "E" + rowIndexString);
            if (this._bankAccount.LastReconciliationActivityDate.HasValue)
            {
                
                this._slDocument.SetCellValue("C" + rowIndexString, this._bankAccount.LastReconciliationActivityDate.Value);
            }
            else
            {
                this._slDocument.SetCellValue("C" + rowIndexString, "Data Not Available");
            }
            SetDateTimeFormatCode(this._summaryValueStyle);
            this._slDocument.SetCellStyle("C" + rowIndexString, "E" + rowIndexString, this._summaryValueStyle);
            ResetFormatCode(this._summaryValueStyle);
            //this._slDocument.AutoFitRow(this._currentDataRowIndex);

            this._currentDataRowIndex++; // VERY IMPORTANT! :)
            rowIndexString = this._currentDataRowIndex.ToString();
            this._slDocument.SetCellValue("A" + rowIndexString, "Latest Reconciled Bank Date");
            this._slDocument.SetCellStyle("A" + rowIndexString, "B" + rowIndexString, this._summaryKeyStyle);
            this._slDocument.MergeWorksheetCells("A" + rowIndexString, "B" + rowIndexString);
            this._slDocument.MergeWorksheetCells("C" + rowIndexString, "E" + rowIndexString);
            if (this._bankAccount.LatestReconciledTransactionBankDate.HasValue)
            {
                this._slDocument.SetCellValue("C" + rowIndexString, this._bankAccount.LatestReconciledTransactionBankDate.Value);
            }
            else
            {
                this._slDocument.SetCellValue("C" + rowIndexString, "Data Not Available");
            }
            SetDateTimeFormatCode(this._summaryValueStyle);
            this._slDocument.SetCellStyle("C" + rowIndexString, "E" + rowIndexString, this._summaryValueStyle);
            ResetFormatCode(this._summaryValueStyle);
            //this._slDocument.AutoFitRow(this._currentDataRowIndex);
           
            this._currentDataRowIndex++; // one empty row
            base.CreateDataRowsHeader();

        }
    }
}
